<?php
/* 
    Project     : 48c6c450f1a4a0cc53d9585dc0fee742
    Created on  : Mar 16, 2013, 11:29:15 PM
    Author      : Truong Khuong - khuongxuantruong@gmail.com
    Description :
        Purpose of the stylesheet follows.
*/

class Backend_Model extends CI_Model {
    private $configs;
    function __construct($table='',$prefix='')
    {
        parent::__construct();
        $this->table=$table;$this->prefix=$prefix;
        $this->configs["strWhere"] = "WHERE TRUE";
        $this->configs["strGroupBy"]="";
        $this->configs["strOrderBy"]="";
        $this->configs["usingLimit"] = true;
        $this->configs["filterfields"]=array();
        $this->configs["fields"]=array();
        $this->configs["datefields"]=array();
    }
    function _get($id){
        $query=$this->db
                ->where($this->prefix.'_id',$id)
                ->get($this->table);
        return $query->row();
    }
    
    function _gets(){
        $query=$this->db
                ->from($this->table)
                ->order_by($this->prefix.'_insert','DESC')
                ->get(); 
        return $query->result();
    }
    function _insert($params){
        $this->db->set($this->prefix.'_insert', 'NOW()', FALSE);
        @$this->db->insert($this->table, $params); 
        @$count = $this->db->affected_rows(); //should return the number of rows affected by the last query
        if($count==1) return true;
        return false;
    }
    function _delete($id){
        $this->db->set($this->prefix.'_delete', 'NOW()', FALSE);
        $where=array($this->prefix.'_id'=>$id);
        $this->db->where($where);
        $this->db->update($this->table); 
        $count = $this->db->affected_rows(); //should return the number of rows affected by the last query
        if($count==1) return true;
        return false;
    }
    function _retore($id){
        $this->db->set($this->prefix.'_delete', 'NULL', FALSE);
        $where=array($this->prefix.'_id'=>$id);
        $this->db->where($where);
        $this->db->update($this->table); 
        $count = $this->db->affected_rows(); //should return the number of rows affected by the last query
        if($count==1) return true;
        return false;
    }
    function _update($id,$params){
        $this->db->set($this->prefix.'_update', 'NOW()', FALSE);
        $this->db->where($this->prefix.'_id', $id);
        @$this->db->update($this->table, $params); 
        @$count = $this->db->affected_rows(); //should return the number of rows affected by the last query
        if($count==1) return true;
        return false;
    }
    function init($configs) {
        if (isset($configs["strQuery"]))
            $this->configs["strQuery"] = $configs["strQuery"];
        if (isset($configs["strWhere"]))
            $this->configs["strWhere"] = $configs["strWhere"];
        if (isset($configs["strOrderBy"]))
            $this->configs["strOrderBy"] = $configs["strOrderBy"];
        if (isset($configs["strGroupBy"]))
            $this->configs["strGroupBy"] = $configs["strGroupBy"];
        if (isset($configs["fields"]))
            $this->configs["fields"] = $configs["fields"];
        if (isset($configs["datefields"]))
            $this->configs["datefields"] = $configs["datefields"];
        if (isset($configs["filterfields"]))
            $this->configs["filterfields"] = $configs["filterfields"];
        if (isset($configs["usingLimit"]))
            $this->configs["usingLimit"] = $configs["usingLimit"];
    }
    function jqxBinding() {
        $FstrSQL = $this->configs["strQuery"];
        $select = $this->configs["strQuery"];
        $where = $this->configs["strWhere"];
        $strgroupby = $this->configs["strGroupBy"];
        $orderby = $this->configs["strOrderBy"];
        $fields = $this->configs["fields"];
        $datefields = $this->configs["datefields"];
        
        $pagenum = isset($_POST['pagenum']) ? $_POST['pagenum'] : 0;
        $pagesize = isset($_POST['pagesize']) ? $_POST['pagesize'] : 100;
        $start = $pagenum * $pagesize;
        $limit = "";
        if (isset($this->configs["usingLimit"]) && $this->configs["usingLimit"]) {
            $limit = "LIMIT $start, $pagesize";
        }
        
        if (isset($_POST['filterscount'])) {
            $filterscount = $_POST['filterscount'];

            if ($filterscount > 0) {
                $where.= " AND (";
                $tmpdatafield = "";
                $tmpfilteroperator = "";
                for ($i = 0; $i < $filterscount; $i++) {
                    // get the filter's value.
                    $filtervalue = $_POST["filtervalue" . $i];
                    // get the filter's condition.
                    $filtercondition = $_POST["filtercondition" . $i];
                    // get the filter's column.
                    $filterdatafield = $_POST["filterdatafield" . $i];
                    // get the filter's operator.
                    $filteroperator = $_POST["filteroperator" . $i];

                    if($filterdatafield[0] === "_" && $filterdatafield[strlen($filterdatafield) - 1] === "_"){
                        $filterdatafield=  substr($filterdatafield, 1, -1);
                    }

                    
                    if (count($datefields) > 0 && in_array($filterdatafield, $datefields)) {
                        $tmp=  explode("GMT", $filtervalue);
                        if(isset($tmp[0])){
                            $filtervalue=date("Y-m-d H:i:s", strtotime($tmp[0]));
                        }
                    }
                    $filtervalue=$this->db->escape_str($filtervalue);
                    if (count($fields) > 0 && isset($fields[$filterdatafield])) {
                        $filterdatafield = $fields[$filterdatafield];
                    } else {
                        $filterdatafield = "`$filterdatafield`";
                    }
                    
                    //check filterdatafield
                    if ($tmpdatafield == "") {
                        $tmpdatafield = $filterdatafield;
                    } else if ($tmpdatafield <> $filterdatafield) {
                        $where .= " ) AND ( ";
                    } else if ($tmpdatafield == $filterdatafield) {
                        if ($tmpfilteroperator == 0) {
                            $where .= " AND ";
                        }
                        else
                            $where .= " OR ";
                    }

                    // build the "WHERE" clause depending on the filter's condition, value and datafield.
                    // possible conditions for string filter: 
                    //      'EMPTY', 'NOT_EMPTY', 'CONTAINS', 'CONTAINS_CASE_SENSITIVE',
                    //      'DOES_NOT_CONTAIN', 'DOES_NOT_CONTAIN_CASE_SENSITIVE', 
                    //      'STARTS_WITH', 'STARTS_WITH_CASE_SENSITIVE',
                    //      'ENDS_WITH', 'ENDS_WITH_CASE_SENSITIVE', 'EQUAL', 
                    //      'EQUAL_CASE_SENSITIVE', 'NULL', 'NOT_NULL'
                    // 
                    // possible conditions for numeric filter: 'EQUAL', 'NOT_EQUAL', 'LESS_THAN',
                    //  'LESS_THAN_OR_EQUAL', 'GREATER_THAN', 'GREATER_THAN_OR_EQUAL', 
                    //  'NULL', 'NOT_NULL'
                    //  
                    // possible conditions for date filter: 'EQUAL', 'NOT_EQUAL', 'LESS_THAN', 
                    // 'LESS_THAN_OR_EQUAL', 'GREATER_THAN', 'GREATER_THAN_OR_EQUAL', 'NULL', 
                    // 'NOT_NULL'                         
                    
                    switch ($filtercondition) {
                        case "NULL":
                            $where .= " ($filterdatafield is null)";
                            break;
                        case "EMPTY":
                            $where .= " ($filterdatafield is null) or ($filterdatafield='')";
                            break;
                        case "NOT_NULL":
                            $where .= " ($filterdatafield is not null)";
                            break;
                        case "NOT_EMPTY":
                            $where .= " ($filterdatafield is not null) and ($filterdatafield <>'')";
                            break;
                        case "CONTAINS_CASE_SENSITIVE":
                        case "CONTAINS":
                            $where .= " $filterdatafield LIKE '%$filtervalue%'";
                            break;
                        case "DOES_NOT_CONTAIN_CASE_SENSITIVE":
                        case "DOES_NOT_CONTAIN":
                            $where .= " $filterdatafield NOT LIKE '%$filtervalue%'";
                            break;
                        case "EQUAL_CASE_SENSITIVE":
                        case "EQUAL":
                            $where .= " $filterdatafield = '$filtervalue'";
                            break;
                        case "NOT_EQUAL":
                            $where .= " $filterdatafield <> '$filtervalue'";
                            break;
                        case "GREATER_THAN":
                            $where .= " $filterdatafield > '$filtervalue'";
                            break;
                        case "LESS_THAN":
                            $where .= " $filterdatafield < '$filtervalue'";
                            break;
                        case "GREATER_THAN_OR_EQUAL":
                            $where .= " $filterdatafield >= '$filtervalue'";
                            break;
                        case "LESS_THAN_OR_EQUAL":
                            $where .= " $filterdatafield <= '$filtervalue'";
                            break;
                        case "STARTS_WITH_CASE_SENSITIVE":
                        case "STARTS_WITH":
                            $where .= " $filterdatafield LIKE '$filtervalue%'";
                            break;
                        case "ENDS_WITH_CASE_SENSITIVE":
                        case "ENDS_WITH":
                            $where .= " $filterdatafield LIKE '%$filtervalue'";
                            break;
                        default:
                            $where .= " $filterdatafield LIKE '%$filtervalue%'";
                    }

                    if ($i == $filterscount - 1) {
                        $where .= ")";
                    }

                    $tmpfilteroperator = $filteroperator;
                    $tmpdatafield = $filterdatafield;
                }
                // build the query.
                
            }
        }

        if (isset($_POST['sortdatafield'])) {
            $sortfield = $_POST['sortdatafield'];
            //fix sortfield
            if($sortfield[0] === "_" && $sortfield[strlen($sortfield) - 1] === "_"){
                $sortfield=  substr($sortfield, 1, -1);
            }

            if (count($fields) > 0 && isset($fields[$sortfield])) {
                $sortfield = $fields[$sortfield];
            } else {
                $sortfield = "`$sortfield`";
            }
            $sortorder = $_POST['sortorder'];
            if ($sortorder == "desc") {
                $SQLquery = "$FstrSQL $where $strgroupby ORDER BY $sortfield DESC $limit";
            } elseif ($sortorder == "asc") {
                $SQLquery = "$FstrSQL $where $strgroupby ORDER BY $sortfield ASC $limit";
            } else {
                $SQLquery = "$FstrSQL $where $strgroupby $orderby $limit";
            }		
        } else {
            $SQLquery = "$FstrSQL $where $strgroupby $orderby $limit";
        }
        $_SESSION["debug"]["SQLquery"]=$SQLquery;
        $query = $this->db->query($SQLquery);
        $result['rows'] = $query->result();
        $sql = "SELECT FOUND_ROWS() AS `found_rows`;";
        $query = $this->db->query($sql);
        $rows = $query->row_array();
        $total_rows = $rows['found_rows'];
        $result['totalrecords'] = $total_rows;
        return $result;
        //$data['total_rows']=$total_rows;
    }
}